<?php

namespace catchAdmin\stock\model;

use catchAdmin\stock\model\search\StockSearch;
use catchAdmin\storehouse\model\Project;
use catcher\base\CatchModel;
use catchAdmin\customs\excel\stockExport;
use catcher\CatchAuth;
use think\Exception;
use think\facade\Db;
/**
 *
 * @property int $id
 * @property string $stock_id
 * @property string $warehousing_id
 * @property string $warehousing_son_id
 * @property string $tray_id
 * @property int $tray_number
 * @property string $config_id
 * @property string $project_id
 * @property string $project_client
 * @property int $number
 * @property int $box_number
 * @property string $goods_id
 * @property string $HSCODE
 * @property string $goods_name
 * @property string $goods_Cname
 * @property string $goods_Ename
 * @property string $supplier_id
 * @property string $supplier_name
 * @property string $manufacturer_id
 * @property string $manufacturer_name
 * @property string $packing_id
 * @property string $p_barcode
 * @property float $packing_num
 * @property string $dictionary_name
 * @property int $created_at
 * @property int $updated_at
 * @property string $status
 * @property string $split_status
 * @property int $Good_Qty
 * @property int $Hold_Qty
 * @property int $NG_Qty
 * @property int $out_number
 * @property int $tenant_id
 * @property int $creator_id
 * @property int $deleted_at
 */
class Stock extends CatchModel
{
    use StockSearch;
    
    public $field = [
        // ID
        'id',
        // 仓库编号
        'stock_id',
        // 入库编号
        'warehousing_id',
        // 入库明细编号
        'warehousing_son_id',
        // 托盘码
        'tray_id',
        // 托盘数
        'tray_number',
        // 库位编号(关联库位表）
        'config_id',
        // 项目id(关联项目表)
        'project_id',
        // 委托方ID(关联项目的二分类)
        'project_client',
        // 数量(对应托盘表入库数量)
        'number',
        // 箱数(对应托盘表入库箱数)
        'box_number',
        // 商品编号
        'goods_id',
        // HSCODE
        'HSCODE',
        // 商品名称
        'goods_name',
        // 商品中文名称
        'goods_Cname',
        // 商品英文名称
        'goods_Ename',
        // 供应商编号
        'supplier_id',
        // 供应商名称
        'supplier_name',
        // 制造商编号
        'manufacturer_id',
        // 制造商名称
        'manufacturer_name',
        // 包装编号
        'packing_id',
        // 包装条形码
        'p_barcode',
        // 包装数量
        'packing_num',
        // 包装单位名称
        'unit_name',
        // 创建时间
        'created_at',
        // 更新时间
        'updated_at',
        // 出库状态:0=未出库,1=全部出库,2=部分出库
        'status',
        // 拆分状态:0=未拆分,1=已拆分
        'split_status',
        // Good_Qty
        'Good_Qty',
        // Hold_Qty
        'Hold_Qty',
        // NG_Qty
        'NG_Qty',
        // 出库数量
        'out_number',
        // 商户id
        'tenant_id',
        // 创建者id
        'creator_id',
        // 删除状态，0未删除 >0 已删除
        'deleted_at',
        'entrepot_id',
        'entrepot_name',
        'batch_number',
        'box_id',
        'slice_id',
        'LOT1',
        'LOT2',
        'LOT3'
    ];
    
    public $name = 'db_stock';
    /**
   * 列表
   *
   * @time 2020年01月09日
   * @param $params
   * @throws \think\db\exception\DbException
   * @return \think\Paginator
   */
    public function getList($tenant_id = 0,$config_name = '',$goods_status = '',$customer_business_number = '')
    {
        $user = (new CatchAuth())->user();
        $user_id = $user->id;
        $type = $user['type'];
        $where = [];
        if($tenant_id !== 0){
            $where[]=['s.tenant_id','=',$tenant_id];

            if($type==3){//员工账号，拿到所有项目
                $projectIds = (new Project)->where([
                    ['pid','>',0],
                    ['tenant_id','=',$tenant_id],
                    ['employees','find in set', $user_id],
                ])->column('project_id');
                $where[] = ['s.project_client','in',$projectIds];
            }
        }
        if(!empty($config_name)){
            $config_id=Db::table('db_warehouse_config')->where('name',$config_name)->value('config_id');
            $where[]=['s.config_id','=',$config_id];
        }
        if(!empty($goods_status)){
            if($goods_status=='Good'){
                $where[]=['s.Good_Qty','>',0];
            }elseif ($goods_status=='Hold'){
                $where[]=['s.Hold_Qty','>',0];
            }elseif ($goods_status=='NG'){
                $where[]=['s.NG_Qty','>',0];
            }
        }
        if(!empty($customer_business_number)){
            $where[]=['w.customer_business_number','=',$customer_business_number];
        }

        //判断是否是客户账号--开始
        $user_id =  (new CatchAuth())->user()->id;
        $role_ids = Db::table('user_has_roles')->where('uid',$user_id)->column('role_id');
        $role_identify = Db::table('roles')->where('id','in',$role_ids)->column('identify');

        if(in_array('customer',$role_identify)){
            $is_exist = Db::table("db_provider")
                ->where('tenant_id',(new CatchAuth())->user()->tenant_id)
                ->where('account_code',(new CatchAuth())->user()->username)->find();
            if($is_exist){
                $projectIds = Db::table('db_project')->where('provider_id',$is_exist['id'])->column('project_id');
                $where[]=['s.project_client','in',$projectIds];
            }
        }
        //判断是否是客户账号--结束

        $par = \request()->param();
        if(!array_key_exists('field_order',$par)){
            $order = ['s.id'=>'desc'];
        }else{
            $order =  $par['field_order'];
        }

       return $this->catchSearch()
                    ->alias('s')
                    ->leftJoin('db_project p','s.project_id = p.project_id')
                    ->leftJoin('db_project pr','s.project_client = pr.project_id')
                    ->leftJoin('db_warehouse_config c','s.config_id = c.config_id')
                    ->leftJoin('db_product g','s.goods_id = g.goods_id')
                   ->leftJoin('db_warehousing w','s.warehousing_id = w.warehousing_id')
                   ->leftJoin('db_warehousing_son ws','s.warehousing_son_id = ws.warehousing_son_id')
           ->leftJoin('db_product product','product.goods_id = s.goods_id')
           ->leftJoin('db_provider sup','sup.id = s.supplier_id ')
           ->leftJoin('db_provider man','man.id = s.manufacturer_id ')
                    ->field(['s.*,c.name,pr.project_name,w.customer_business_number,w.relation_order,g.item_number_system,ws.batch_number',
                        'sup.account_name as _supplier_name','man.account_name as _manufacturer_name','product.item_number_system'
                        ])
//                    ->order('s.id','desc')
                    ->order($order)
                    ->where($where)
                    ->paginate();
    }
    /**
     * 订单导出关联列表
     * @time 2020年01月09日
     * @param int $page 页码
     * @param int $limit 每页数量
     */
    public function getExportList($page = 1, $limit = 1000)
    {
        $CatchAuth = new CatchAuth();
        $user = $CatchAuth->user();
        $type = $user['type'];
        $tenant_id = $user['tenant_id'];
        $user_id = $user->id;
        $where[] =  ['s.deleted_at','=',0];
        //非管理员要商户id
        if($CatchAuth->user()['type'] !== 1 ){
            $where[] =  ['s.tenant_id','=',$tenant_id];

            if($type==3){//员工账号，拿到所有项目
                $projectIds = (new Project)->where([
                    ['pid','>',0],
                    ['tenant_id','=',$tenant_id],
                    ['employees','find in set', $user_id],
                ])->column('project_id');
                $where[] = ['s.project_client','in',$projectIds];
            }
        }
        return $this->catchSearch()
            ->alias('s')
            ->leftJoin('db_project p','s.project_id = p.project_id')
            ->leftJoin('db_project pr','s.project_client = pr.project_id')
            ->leftJoin('db_warehouse_config c','s.config_id = c.config_id')
            ->leftJoin('db_product g','s.goods_id = g.goods_id')
            ->leftJoin('db_warehousing w','s.warehousing_id = w.warehousing_id')
            ->leftJoin('db_warehousing_son ws','s.warehousing_son_id = ws.warehousing_son_id')
        ->leftJoin('db_product product','product.goods_id = s.goods_id')
        ->leftJoin('db_provider sup','sup.id = s.supplier_id ')
        ->leftJoin('db_provider man','man.id = s.manufacturer_id ')
            ->field('pr.project_name,
            g.owner,
            s.tray_id,
            ws.batch_number,
            s.goods_Cname,
            s.goods_name,
           s.packing_num,
           s.unit_name,
            s.number,
            s.box_number,
            c.name,
            s.created_at,
            w.customer_business_number,
            ws.lot01,
            ws.lot02,
            s.Good_Qty,
            s.Hold_Qty,
            s.NG_Qty,
            s.tray_number,
            s.out_number,
            s.status,
            w.take_date,
            s.LOT1,
            s.LOT2,
            s.LOT3,
            product.item_number_system,
            sup.account_name as _supplier_name,
            man.account_name as _manufacturer_name'
            )
            //->field(['s.*,c.name,pr.project_name,w.customer_business_number,w.relation_order,g.item_number_system,ws.batch_number'])
            //->order('s.id','desc')
            ->where($where)
            ->page($page, $limit)  // 添加分页
            ->select()
            ->toArray();
    }

    public function splitBox($stock_id,$num=1,$generate_ids=[]){
        $user = (new CatchAuth)->user();
        $user_co_code = Db::table('users')->where('id',$user['tenant_id'])->value('co_code');


        $stock = (new Stock)->where([
            ['stock_id','=',$stock_id],
            ['box_id','=',0],//说明是箱
            ['slice_id','=',0],//必须是件
            ['status','<>','1']//不能全部出库
        ])->find();

        if(empty($stock)){
            throw new Exception('错误库存');
        }

        $packing = Db::table('db_packing')->where('packing_id',$stock->packing_id)->find();


        //剩余数量,设计错误，不能通过 数量-已出库数量，只能通过其他字段判断，
        //$stock_sy = $stock['number'] - $stock['out_number'];
        $stock_sy = (int)array_sum([$stock['Good_Qty'],$stock['Hold_Qty'],$stock['NG_Qty']]);


        //剩余箱数（可能出库过，所以要重新计算，不能直接获取）
        $stock_box_sy = ceil($stock_sy / $stock->packing_num);

        if($num > $stock_box_sy){
            throw new Exception('箱数错误');
        }

        $stock_temp = $stock_sy;

        $new = [];

        if($stock_temp > $num * $stock->packing_num){
            $old = $stock_temp - $num * $stock->packing_num;
            $new =  array_pad($new,$num,$stock->packing_num);
        }else{
            $old = $stock_temp - ($num - 1) * $stock->packing_num;
            if($num - 1 > 0){
                $new = array_pad($new,$num-1,$stock->packing_num);
            }
        }

        //dd($old);

        //新增
        $box_id = 1;

        $newInsertArr = [];
        if(!empty($new)){
            $stock_res_data = $stock->toArray();
            foreach ($new as $key => $value){
                $data = $stock_res_data;
                unset($data['id']);

                $data['stock_id'] =  $user_co_code .  date('YmdHis').mt_rand(10000,99999) . $key;
                $data['tray_number'] = 1;
                $data['number'] = $value;
                $data['box_number'] = 1;
                $data['created_at'] = time();
                $data['updated_at'] = time();
                $data['status'] = 0;
                $data['split_status'] = 0;

                if(0 != $stock_res_data['Good_Qty']){
                    $data['Good_Qty'] = $value;
                }elseif(0 != $stock_res_data['Hold_Qty']){
                    $data['Hold_Qty'] = $value;
                }elseif (0 != $stock_res_data['NG_Qty']){
                    $data['NG_Qty'] = $value;
                }

                $data['out_number'] = 0;
                $data['deleted_at'] = 0;

                //  箱号处理
                if(empty($generate_ids)){//自动生成
                    $data['box_id'] = $user_co_code .  "B" . date('YmdHis').mt_rand(10000,99999) . $key;
                }else{//手动填写
                    //判断箱号是否存在
                    if(!empty(Db::table('db_stock')->where('box_id',$generate_ids[$key])->find())){
                        throw new Exception("箱号{$generate_ids[$key]}重复，请检查");
                    }
                    $data['box_id'] = $generate_ids[$key];
                }

                $newInsertArr[] = $data;

                $box_id++;
            }
        }

        //dd($stock->toArray());

       // return  array_merge(array_,$newInsertArr);

        Db::startTrans();
        try {
            //插入新
            if(!empty($newInsertArr)){
                Db::table('db_stock')->insertAll($newInsertArr);
            }
            //修改旧
            $stock->number = $old;
            $stock->box_number = ceil( $old / $stock->packing_num );
            if(0 != $stock->Good_Qty){
                $stock->Good_Qty = $old;
            }elseif(0 !=$stock->Hold_Qty){
                $stock->Hold_Qty = $old;
            }elseif (0 != $stock->NG_Qty){
                $stock->NG_Qty = $old;
            }
            if($stock_box_sy == $num){

                //箱号-处理
                if(empty($generate_ids)){//自动生成
                    $stock->box_id =  $user_co_code .  "B" . date('YmdHis').mt_rand(10000,99999);
                }else{//手动填写
                    //判断箱号是否存在
                    $temp_arr_count = count($generate_ids) - 1;
                    if(!empty(Db::table('db_stock')->where('box_id',$generate_ids[$temp_arr_count])->find())){
                        Db::rollback();
                        throw new Exception("箱号{$generate_ids[$temp_arr_count]}重复，请检查");
                    }
                    $stock->box_id = $generate_ids[0];
                }

            }



            $stock->save();

            Db::commit();
        } catch (\Exception $exception) {
            Db::rollback();
            throw new Exception('拆箱失败' . $exception->getMessage());
        }

        //添加前端要的数据
        if(!empty($newInsertArr)){
            foreach ($newInsertArr as &$value){
                $value['_stock_num'] = array_sum([$value['Good_Qty'],$value['Hold_Qty'],$value['NG_Qty']]);
                $value['config_name'] = Db::table('db_warehouse_config')->where('config_id',$value['config_id'])->value('name');
                $value['_stock_type'] = 'box';
            }
        }

        $stock_1[] = $stock->toArray();

        //添加前端要的数据
        $stock_1[0]['config_name'] =  Db::table('db_warehouse_config')->where('config_id',$stock_1[0]['config_id'])->value('name');
        $stock_1[0]['_stock_num'] = array_sum([$stock_1[0]['Good_Qty'],$stock_1[0]['Hold_Qty'],$stock_1[0]['NG_Qty']]);
        $stock_1[0]['_stock_type'] = empty($stock_1[0]['box_id']) ? 'tray' : 'box';
        $stock_1[0]['box_id'] = empty($stock_1[0]['box_id']) ? null : $stock_1[0]['box_id'];
        $stock_1[0]['slice_id'] = null;

        return  array_merge($stock_1,$newInsertArr);
    }

    //拆件
    public function splitSlice($stock_id,$num=1,$generate_ids=[]){
        $user = (new CatchAuth)->user();
        $user_co_code = Db::table('users')->where('id',$user['tenant_id'])->value('co_code');

        $stock = (new Stock)->where([
            ['stock_id','=',$stock_id],
            ['status','<>','1'],//不能全部出库
            ['box_id','<>',0],//说明是箱
            ['slice_id','=',0]//必须是件
        ])->find();

        if(empty($stock)){
            throw new Exception('错误库存');
        }
        //剩余数量,设计错误，不能通过 数量-已出库数量，只能通过其他字段判断，
        //$stock_sy = $stock['number'] - $stock['out_number'];
        $stock_sy = (int)array_sum([$stock['Good_Qty'],$stock['Hold_Qty'],$stock['NG_Qty']]);

        if($num > $stock_sy){
            throw new Exception('库存不足，拆件数量不足够');
        }

        $new = [];
        if($num == $stock_sy){//全拆
            $old = 1;
            $new =  array_pad($new,$num-1,1);
        }else{
            $old = $stock_sy-$num;
            $new = array_pad($new,$num,1);
        }

        $newInsertArr = [];
        if(!empty($new)){
            $stock_res_data = $stock->toArray();
            foreach ($new as $key => $value){
                $data = $stock_res_data;
                unset($data['id']);
                $data['stock_id'] =  $user_co_code .  date('YmdHis').mt_rand(10000,999999) . $key;
                $data['tray_number'] = 1;
                $data['number'] = $value;
                $data['box_number'] = 1;
                $data['created_at'] = time();
                $data['updated_at'] = time();
                $data['status'] = 0;
                $data['split_status'] = 0;

                if(0 != $stock_res_data['Good_Qty']){
                    $data['Good_Qty'] = $value;
                }elseif(0 != $stock_res_data['Hold_Qty']){
                    $data['Hold_Qty'] = $value;
                }elseif (0 != $stock_res_data['NG_Qty']){
                    $data['NG_Qty'] = $value;
                }

                $data['out_number'] = 0;
                $data['deleted_at'] = 0;

                //处理片号
                if(empty($generate_ids)){//自动生成
                    $data['slice_id'] = $user_co_code .  "S" . date('YmdHis').mt_rand(10000,99999) . $key;
                }else{//手动填写
                    //判断片号是否存在
                    if(!empty(Db::table('db_stock')->where('slice_id',$generate_ids[$key])->find())){
                        throw new Exception("片号{$generate_ids[$key]}重复，请检查");
                    }
                    $data['slice_id'] = $generate_ids[$key];
                }

                $newInsertArr[] = $data;
            }
        }

        Db::startTrans();
        try {
            //插入新
            if(!empty($newInsertArr)){
                Db::table('db_stock')->insertAll($newInsertArr);
            }
            //修改旧
            $stock->number = $old;
            $stock->box_number = ceil( $old / $stock->packing_num );
            if(0 != $stock->Good_Qty){
                $stock->Good_Qty = $old;
            }elseif(0 !=$stock->Hold_Qty){
                $stock->Hold_Qty = $old;
            }elseif (0 != $stock->NG_Qty){
                $stock->NG_Qty = $old;
            }
            if($num == $stock_sy){

                //处理片号
                if(empty($generate_ids)){//自动生成
                    $stock->slice_id =  $user_co_code .  "S" . date('YmdHis').mt_rand(1000,9999);
                }else{//手动填写
                    //判断片号是否存在
                    $temp_arr_count = count($generate_ids) - 1;
                    if(!empty(Db::table('db_stock')->where('slice_id',$generate_ids[$temp_arr_count])->find())){
                        Db::rollback();
                        throw new Exception("片号{$generate_ids[$temp_arr_count]}重复，请检查");
                    }
                    $stock->slice_id = $generate_ids[0];
                }

            }
            $stock->save();

            Db::commit();
        } catch (\Exception $exception) {
            Db::rollback();
            throw new Exception('拆件失败' . $exception->getMessage());
        }

        //添加前端要的数据
        if(!empty($newInsertArr)){
            foreach ($newInsertArr as &$value){
                $value['_stock_num'] = array_sum([$value['Good_Qty'],$value['Hold_Qty'],$value['NG_Qty']]);
                $value['config_name'] = Db::table('db_warehouse_config')->where('config_id',$value['config_id'])->value('name');
                $value['_stock_type'] = 'slice';
            }
        }

        $stock_1[] = $stock->toArray();

        //添加前端要的数据
        $stock_1[0]['config_name'] =  Db::table('db_warehouse_config')->where('config_id',$stock_1[0]['config_id'])->value('name');
        $stock_1[0]['_stock_num'] = array_sum([$stock_1[0]['Good_Qty'],$stock_1[0]['Hold_Qty'],$stock_1[0]['NG_Qty']]);
        $stock_1[0]['_stock_type'] = empty($stock_1[0]['slice_id']) ? 'box' : 'slice';
        $stock_1[0]['slice_id'] = empty($stock_1[0]['slice_id']) ? null : $stock_1[0]['slice_id'];

        return  array_merge($stock_1,$newInsertArr);
    }

    public function  getStockIdsByWarehousingSonId($warehousing_son_id,$stock_status = [0,2]){

        $result = Db::table('db_stock')
            ->where('warehousing_son_id','=',$warehousing_son_id)
            ->where('status','in',$stock_status)
            ->where('deleted_at','=',0)
            ->column('stock_id')
        ;

        return $result;
    }

    public function getTotalStockNumber(){
        $user = (new CatchAuth)->user();

        $stockModel = new Stock;
        $where = [
            ['deleted_at','=',0],
            ['status','<>','1']
        ];

        if($user['type'] !== 1) {//普通商户
            $where[] = ['tenant_id','=',$user['tenant_id']];
        }

        $statStock = $stockModel->field('IFNULL(SUM(Good_Qty + Hold_Qty + NG_Qty),0) as number')->where($where)->find();
        return $statStock['number'];
    }

    // 库存库位分布
    public function distribution(){
        $user = (new CatchAuth)->user();

        $where_stock = [
            ['s.deleted_at','=',0],
            ['s.status','<>','1'],
        ];

        if($user['type'] !== 1) {//普通商户
            $where_stock[] = ['s.tenant_id','=',$user['tenant_id']];
        }

        $result = Db::table('db_stock')
            ->alias('s')
            ->field('SUM(Good_Qty + Hold_Qty + NG_Qty) as number,
            ANY_VALUE(wc3.name) as config_name,
            ANY_VALUE(wc3.config_id) as config_id'
            )
            ->leftJoin('db_warehouse_config wc','wc.config_id = s.config_id')
            ->leftJoin('db_warehouse_config wc2','wc2.id = wc.parent_id')
            ->leftJoin('db_warehouse_config wc3','wc3.id = wc2.parent_id')
            ->group('wc3.id')
            ->where($where_stock)
            ->select()->toArray()
        ;

        return $result;
    }
}